package com.hwh.traffic.db;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Build;

import androidx.annotation.RequiresApi;

import com.hwh.traffic.busEntity.Like;
import com.hwh.traffic.busEntity.Record;

import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

public class TrafficLab {

    private Context mContext;
    private SQLiteDatabase mDatabase;

    public TrafficLab(Context context){
        mContext = context.getApplicationContext();
        mDatabase = new TrafficBaseHelper(mContext).getWritableDatabase();

    }

    /**
     * @param stopName
     * @return 站点ID
     * @date 2019年12月11日16:45:24
     */
    public List<Long> findStopIdByName(String stopName){

        String sql = "SELECT stop_id FROM stop WHERE stop_name == '"+stopName+"'";
        //执行查询
        Cursor cursor = mDatabase.rawQuery(sql,null);
        Long stop_id = 0l;
        List<Long> stop_ids = new LinkedList<>();
        try {
            while (cursor.moveToNext()){
                stop_id = cursor.getLong(0);
                stop_ids.add(stop_id);
            }
            cursor.close();
            return stop_ids;
        }catch (Exception e){
            cursor.close();
            return null;
        }

    }

    /**
     *
     * @param routeName 路线名称
     * @return  站点正向ID 和 反向ID
     */
    public Long[] findRouteIdAndOppositeIdByRouteName(String routeName){
        String sql = "SELECT route_id,opposite_id FROM route WHERE route_name LIKE '%"+routeName+"%'";

        Cursor cursor = mDatabase.rawQuery(sql,null);
        if (cursor.moveToNext()){
            Long route_id = cursor.getLong(0);
            Long oppsite_id = cursor.getLong(1);
            cursor.close();
            return new Long[]{route_id,oppsite_id};
        }
        else return null;

    }

    @RequiresApi(api = Build.VERSION_CODES.JELLY_BEAN)
    public Cursor findStopCuror(String stopName){
        String sql = "SELECT _id,stop_name FROM stop GROUP BY stop_name HAVING stop_name LIKE '%"+stopName+"%'";
        //return mDatabase.query(true,"stop",new String[]{"_id","stop_name"},"stop_name",new String[]{stopName},"stop_name",null,null,null,null);
        return mDatabase.rawQuery(sql,null);
    }

    public Cursor findStopAndRoute(String keyWords){
        String sql = "SELECT group_concat(distinct name) as name,_id from (SELECT route_name as name,rid as _id from route r UNION SELECT stop_name as name,_id as _id from stop s order by _id) r WHERE name LIKE '%"+keyWords+"%' group by name";
        //String sql = "SELECT _id,name from (SELECT route_name as name from route r UNION SELECT stop_name as name from stop s) WHERE name LIKE '%"+keyWords+"%'";
        return mDatabase.rawQuery(sql,null);
    }


    /**
     * 更新收藏状态字段
     * @param routeName
     */
    public void updateLikeStatus(String routeName){
        String sql_like = "SELECT is_like from route where route_name like '"+routeName+"%'";
        Cursor cursor = mDatabase.rawQuery(sql_like, null);
        if (cursor.moveToNext()){
            int status = cursor.getInt(0);
            if (status == 0){
                String sql = "UPDATE route set is_like = "+1+" where route_name like '"+routeName+"%'";
                mDatabase.execSQL(sql);
            }
            else {
                String sql = "UPDATE route set is_like = "+0+" where route_name like '"+routeName+"%'";
                mDatabase.execSQL(sql);
            }
            cursor.close();
        }

    }

    public int findLikeStatus(String routeName){

        String sql_like = "SELECT is_like from route where route_name like '"+routeName+"%'";
        Cursor cursor = mDatabase.rawQuery(sql_like, null);
        if (cursor.moveToNext()){
            int status = cursor.getInt(0);
            cursor.close();
            return status;
        }
        cursor.close();
        return -1;
    }


    public Long findStopIdByStopName(String stopName){
        String sql = "SELECT stop_id from stop where stop_name like '%"+stopName+"%'";
        Cursor cursor = mDatabase.rawQuery(sql, null);
        if (cursor.moveToNext()){
            Long stopId = cursor.getLong(0);
            return stopId;
        }
        cursor.close();
        return 1l;
    }


    /**
     * @description 插入一条搜索记录
     * @param routeName
     * @param endName
     */
    public void saveRecord(String routeName,String endName){
        String checkSql = "SELECT search_route FROM search where search_route = '"+routeName+"'";
        Cursor cursor = mDatabase.rawQuery(checkSql, null);
        if (cursor.moveToNext()){
            return;
        }
        String sql = "INSERT INTO search ('search_route','search_end') VALUES ('"+routeName+"','"+endName+"')";
        System.out.println(sql);
        mDatabase.execSQL(sql);
    }

    public List<Record> findAllRecord(){
        List<Record> list = new ArrayList<>();
        String sql = "SELECT search_id,search_route,search_end FROM search";
        Cursor cursor = mDatabase.rawQuery(sql,null);
        while (cursor.moveToNext()){
            Record record = new Record();
            int search_id = cursor.getInt(0);
            String search_route = cursor.getString(1);
            String search_end = cursor.getString(2);
            record.setRecord_id(search_id);
            record.setSearch_route(search_route);
            record.setSearch_end(search_end);
            list.add(record);
        }
        cursor.close();
        return list;
    }

    public void saveLike(String routeName,String endName){
        String checkSql = "SELECT like_route FROM liked where like_route = '"+routeName+"'";
        Cursor cursor = mDatabase.rawQuery(checkSql, null);
        if (cursor.moveToNext()){
            return;
        }
        String sql = "INSERT INTO liked ('like_route','like_end') VALUES ('"+routeName+"','"+endName+"')";
        System.out.println(sql);
        mDatabase.execSQL(sql);
    }

    public List<Like> findAllLike(){
        List<Like> list = new ArrayList<>();
        String sql = "SELECT like_id,like_route,like_end FROM liked";
        Cursor cursor = mDatabase.rawQuery(sql,null);
        while (cursor.moveToNext()){
            Like like = new Like();
            int like_id = cursor.getInt(0);
            String like_route = cursor.getString(1);
            String like_end = cursor.getString(2);
            like.setLike_id(like_id);
            like.setLike_route(like_route);
            like.setLike_end(like_end);
            list.add(like);
        }
        cursor.close();
        return list;
    }


    public void deleteRecord(Integer record_id) {
        String sql = "DELETE FROM search where search_id = "+record_id;
        mDatabase.execSQL(sql);
    }

    public void deleteLike(String routeName) {
        String sql = "DELETE FROM liked where like_route = '"+routeName+"'";
        System.out.println(sql);
        mDatabase.execSQL(sql);
    }
}
